<!DOCTYPE html><html><head><title>MySQL学习笔记（Day006：存储引擎二/多实例安装）</title><meta charset='utf-8'><link href='https://dn-maxiang.qbox.me/res-min/themes/marxico.css' rel='stylesheet'></head><body><div id='preview-contents' class='note-content'>
                        <div id="wmd-preview" class="preview-content"></div>
                    <div id="wmd-preview-section-1" class="wmd-preview-section preview-content">

</div><div id="wmd-preview-section-2" class="wmd-preview-section preview-content">

<h1 id="mysql学习笔记day006存储引擎二多实例安装">MySQL学习笔记（Day006：存储引擎二/多实例安装）</h1>

<p><p class="note-tags "><code class="notebook">MySQL学习</code> </p></p>

<div><div class="toc"><div class="toc">
<ul>
<li><a href="#mysql学习笔记day006存储引擎二多实例安装">MySQL学习笔记（Day006：存储引擎二/多实例安装）</a><ul>
<li><a href="#一-myisam存储引擎下">一. MyISAM存储引擎(下)</a><ul>
<li><a href="#1-myisam还在使用的原因">1. MyISAM还在使用的原因</a></li>
<li><a href="#2-myisam文件组成">2. MyISAM文件组成</a></li>
<li><a href="#3-myisamchk">3. myisamchk</a></li>
</ul>
</li>
<li><a href="#二-memory存储引擎">二. Memory存储引擎</a><ul>
<li><a href="#1-memory介绍">1. Memory介绍</a></li>
<li><a href="#2-memory特性">2. Memory特性</a></li>
<li><a href="#3-memory的物理特性">3. Memory的物理特性</a></li>
</ul>
</li>
<li><a href="#三-csv存储引擎">三. CSV存储引擎</a><ul>
<li><a href="#1-csv介绍">1. CSV介绍</a></li>
<li><a href="#2-csv文件组成">2. CSV文件组成</a></li>
<li><a href="#2-csv特性">2. CSV特性</a></li>
</ul>
</li>
<li><a href="#四federated存储引擎">四.Federated存储引擎</a><ul>
<li><a href="#1-federated介绍">1. Federated介绍</a></li>
<li><a href="#2-federated-语法">2. Federated 语法</a></li>
</ul>
</li>
<li><a href="#五-多实例安装">五. 多实例安装</a><ul>
<li><a href="#1-多实例介绍">1.  多实例介绍</a></li>
<li><a href="#2-安装要求">2. 安装要求</a></li>
<li><a href="#3-安装操作">3. 安装操作</a></li>
</ul>
</li>
<li><a href="#六-federated测试">六. Federated测试</a></li>
</ul>
</li>
</ul>
</div>
</div>
</div>

</div><div id="wmd-preview-section-3" class="wmd-preview-section preview-content">

<h2 id="一-myisam存储引擎下">一. MyISAM存储引擎(下)</h2>

</div><div id="wmd-preview-section-4" class="wmd-preview-section preview-content">

<h3 id="1-myisam还在使用的原因">1. MyISAM还在使用的原因</h3>

<ul><li>历史原因，需要逐步替换</li>
<li>部分如User，DB等系统表(MyISAM引擎)，可以直接拷贝，比较方便</li>
<li>性能好，或者存储小<code>不是</code>MyISAM的优点，也不是存在的原因</li>
</ul>

</div><div id="wmd-preview-section-5" class="wmd-preview-section preview-content">

<h3 id="2-myisam文件组成">2. MyISAM文件组成</h3>

<ul><li><code>frm</code> 表结构文件</li>
<li><code>MYI</code> 索引文件</li>
<li><code>MYD</code> 数据文件 <br>
<ul>
<li>数据文件是堆表数据结构，堆是无序数据的集合</li>
<li><code>MYI</code>中的叶子节点，指向<code>MYD</code>中的数据页</li>
<li>当数据移动到页外时，需要修改对应指针</li></ul></li>
</ul>

</div><div id="wmd-preview-section-6" class="wmd-preview-section preview-content">

<h3 id="3-myisamchk">3. myisamchk</h3>

<p><strong><code>myisamchk</code>通过扫描MYD文件来重建MYI文件；如果MYD文件中某条记录有问题，将跳过该记录</strong></p>

<hr>

</div><div id="wmd-preview-section-7" class="wmd-preview-section preview-content">

<h2 id="二-memory存储引擎">二. Memory存储引擎</h2>

</div><div id="wmd-preview-section-8" class="wmd-preview-section preview-content">

<h3 id="1-memory介绍">1. Memory介绍</h3>

<ul><li>全内存存储的引擎</li>
<li>数据库重启后数据丢失</li>
<li>支持哈希索引</li>
<li>不支持事物</li>
</ul>

</div><div id="wmd-preview-section-9" class="wmd-preview-section preview-content">

<h3 id="2-memory特性">2. Memory特性</h3>

<ul><li><strong><code>千万不要用Memory存储引擎去做缓存(Cache)</code>, 性能上不及Redis和Memcahced</strong></li>
<li>Memory<code>不能禁用</code>，当涉及内部排序操作的临时表时，使用该存储引擎 <br>
<ul>
<li><code>max_heap_table_size</code>决定使用内存的大小，默认时<code>16M</code> <br>
<ul>
<li>无论该表使用的什么引擎，只要使用到临时表，或者指定Memory，都受参数影响</li></ul></li>
<li>当上面设置的内存放不下数据时，(&gt;=5.6)转为MyISAM,(&gt;=5.7)转为InnoDB <br>
<ul>
<li>注意磁盘上临时路径空间的大小(<code>tmpdir</code>)</li></ul></li>
<li>内存使用为会话(SESSION)级别，当心内核OOM</li></ul></li>
<li>支持哈希索引，且仅支持等值查询</li>
</ul>

</div><div id="wmd-preview-section-10" class="wmd-preview-section preview-content">

<pre class="prettyprint hljs-dark"><code class="language-sql hljs">mysql&gt; <span class="hljs-operator"><span class="hljs-keyword">show</span> <span class="hljs-keyword">global</span> <span class="hljs-keyword">status</span> <span class="hljs-keyword">like</span> <span class="hljs-string">"%tmp%tables"</span>;</span><br>+<span class="hljs-comment">-------------------------+-------+</span><br>| Variable_name           | Value |<br>+<span class="hljs-comment">-------------------------+-------+</span><br>| Created_tmp_disk_tables | 0     |   <span class="hljs-comment">-- 内存放不下，转成磁盘存储的数量,如果过大，考虑增大内存参数</span><br>| Created_tmp_tables      | 4     |   <span class="hljs-comment">-- 创建临时表的数量</span><br>+<span class="hljs-comment">-------------------------+-------+</span><br>2 rows in <span class="hljs-operator"><span class="hljs-keyword">set</span> (<span class="hljs-number">0.00</span> sec)<br><br>mysql&gt; <span class="hljs-keyword">show</span> <span class="hljs-keyword">variables</span> <span class="hljs-keyword">like</span> <span class="hljs-string">'tmpdir'</span>;</span><br>+<span class="hljs-comment">---------------+-------+</span><br>| Variable_name | Value |<br>+<span class="hljs-comment">---------------+-------+</span><br>| tmpdir        | /tmp  |  <span class="hljs-comment">-- memory转成磁盘存储的路径</span><br>+<span class="hljs-comment">---------------+-------+</span><br>1 row in <span class="hljs-operator"><span class="hljs-keyword">set</span> (<span class="hljs-number">0.00</span> sec)<br><br>mysql&gt; <span class="hljs-keyword">show</span> <span class="hljs-keyword">create</span> <span class="hljs-keyword">table</span> <span class="hljs-keyword">User</span>\G<br>*************************** <span class="hljs-number">1.</span> <span class="hljs-keyword">row</span> ***************************<br><span class="hljs-keyword">Table</span>: <span class="hljs-keyword">User</span><br><span class="hljs-keyword">Create</span> <span class="hljs-keyword">Table</span>: <span class="hljs-keyword">CREATE</span> <span class="hljs-keyword">TABLE</span> <span class="hljs-string">`User`</span> (<br><span class="hljs-string">`id`</span> <span class="hljs-built_in">int</span>(<span class="hljs-number">11</span>) <span class="hljs-keyword">NOT</span> <span class="hljs-literal">NULL</span>,<br><span class="hljs-string">`name`</span> <span class="hljs-built_in">varchar</span>(<span class="hljs-number">128</span>) <span class="hljs-keyword">DEFAULT</span> <span class="hljs-literal">NULL</span>,<br><span class="hljs-keyword">PRIMARY</span> <span class="hljs-keyword">KEY</span> (<span class="hljs-string">`id`</span>),<br><span class="hljs-keyword">KEY</span> <span class="hljs-string">`name`</span> (<span class="hljs-string">`name`</span>) <span class="hljs-keyword">USING</span> HASH  <span class="hljs-comment">-- 对这个字段使用USING HASH,创建hash索引</span><br>) <span class="hljs-keyword">ENGINE</span>=MEMORY <span class="hljs-keyword">DEFAULT</span> <span class="hljs-keyword">CHARSET</span>=latin1<br><span class="hljs-number">1</span> <span class="hljs-keyword">row</span> <span class="hljs-keyword">in</span> <span class="hljs-keyword">set</span> (<span class="hljs-number">0.00</span> sec)</span><br></code></pre>

</div><div id="wmd-preview-section-11" class="wmd-preview-section preview-content">

<h3 id="3-memory的物理特性">3. Memory的物理特性</h3>

<ul><li>内存不会一次性分配最大空间，而是随着使用逐步增到到最大值</li>
<li>通过链表管理空闲空间</li>
<li>使用固定长度存储数据</li>
<li>不支持BLOB和TEXT类型</li>
<li>可以创建自增主键</li>
</ul>

<hr>

</div><div id="wmd-preview-section-12" class="wmd-preview-section preview-content">

<h2 id="三-csv存储引擎">三. CSV存储引擎</h2>

</div><div id="wmd-preview-section-13" class="wmd-preview-section preview-content">

<h3 id="1-csv介绍">1. CSV介绍</h3>

<ul><li>CSV - Comma-Separated Values，使用逗号分隔</li>
<li>不支持特殊字符</li>
<li>CSV是一种标准文件格式</li>
<li>文件以纯文本形式存储表格数据</li>
<li>使用广泛</li>
</ul>

</div><div id="wmd-preview-section-14" class="wmd-preview-section preview-content">

<h3 id="2-csv文件组成">2. CSV文件组成</h3>

<ul><li><code>frm</code> 表结构</li>
<li><code>CSV</code> 数据文件</li>
<li><code>CSM</code> 元数据信息</li>
</ul>

</div><div id="wmd-preview-section-15" class="wmd-preview-section preview-content">

<h3 id="2-csv特性">2. CSV特性</h3>

<ul><li>MySQL CSV存储引擎运行时，即创建CSV<code>文件</code></li>
<li>通过MySQL标准接口来查看和修改CSV文件</li>
<li>无需将CSV文件导入到数据库，只需创建相同字段的表结构，拷贝CSV文件即可</li>
<li>CSV存储引擎表每个字段<code>必须是NOT NULL</code>属性</li>
</ul>

<hr>

</div><div id="wmd-preview-section-16" class="wmd-preview-section preview-content">

<h2 id="四federated存储引擎">四.Federated存储引擎</h2>

</div><div id="wmd-preview-section-17" class="wmd-preview-section preview-content">

<h3 id="1-federated介绍">1. Federated介绍</h3>

<ul><li>允许本地访问远程MySQL数据库中表的数据</li>
<li>本地不存储任何数据文件</li>
<li>类似Oracle中的DBLink</li>
<li>Federated存储引擎默认不开启, 需要在<code>my.cnf</code>的<code>[mysqld]</code>标签下添加 <code>federated</code></li>
<li>MySQL的Federated不支持异构数据库访问，MariaDB中的<code>FederatedX</code>支持</li>
</ul>

</div><div id="wmd-preview-section-18" class="wmd-preview-section preview-content">

<h3 id="2-federated-语法">2. Federated 语法</h3>

<p><code>scheme://user_name[:password]@host_name[:port_num]/db_name/tbl_name</code></p>

<p><code>CONNECTION='mysql://username:password@hostname:port/database/tablename'</code></p>

</div><div id="wmd-preview-section-19" class="wmd-preview-section preview-content">

<pre class="prettyprint hljs-dark"><code class="language-sql hljs"><span class="hljs-comment">--</span><br><span class="hljs-comment">-- 例子</span><br><span class="hljs-comment">--</span><br><span class="hljs-operator"><span class="hljs-keyword">CREATE</span> <span class="hljs-keyword">TABLE</span> <span class="hljs-string">`T1`</span> (<br><span class="hljs-string">`A`</span> <span class="hljs-built_in">VARCHAR</span>(<span class="hljs-number">100</span>),<br><span class="hljs-keyword">UNIQUE</span> <span class="hljs-keyword">KEY</span> (<span class="hljs-string">`A`</span> (<span class="hljs-number">30</span>))<br>) <span class="hljs-keyword">ENGINE</span>=FEDERATED<br><span class="hljs-keyword">CONNECTION</span>=<span class="hljs-string">'MYSQL://david:123@127.0.0.1:3306/TEST/T1'</span>;</span><br></code></pre>

<hr>

</div><div id="wmd-preview-section-20" class="wmd-preview-section preview-content">

<h2 id="五-多实例安装">五. 多实例安装</h2>

</div><div id="wmd-preview-section-21" class="wmd-preview-section preview-content">

<h3 id="1-多实例介绍">1.  多实例介绍</h3>

<ul><li>一台服务器上安装多个MySQL数据库实例</li>
<li>可以充分利用服务器的硬件资源</li>
<li>通过mysqld_multi进行管理</li>
</ul>

</div><div id="wmd-preview-section-22" class="wmd-preview-section preview-content">

<h3 id="2-安装要求">2. 安装要求</h3>

<ul><li><p>MySQL实例1 - <code>mysql1</code></p>

<ul>
<li>port = 3306</li>
<li>datadir = /data1</li>
<li>socket = /tmp/mysql.sock1</li></ul></li>
<li><p>MySQL实例2 - <code>mysql2</code></p>

<ul>
<li>port = 3307</li>
<li>datadir = /data2</li>
<li>socket = /tmp/mysql.sock2</li></ul></li>
<li><p>MySQL实例3 - <code>mysql3</code></p>

<ul>
<li>port = 3308</li>
<li>datadir = /data3</li>
<li>socket = /tmp/mysql.sock3</li></ul></li>
<li><p>MySQL实例4 - <code>mysql4</code></p>

<ul>
<li>port = 3309</li>
<li>datadir = /data4</li>
<li>socket = /tmp/mysql.sock4</li></ul></li>
</ul>

<blockquote>
  <p><strong><code>该三个参数必须定制，且必须不同 (port / datadir / socket)</code></strong> <br>
  <code>server-id</code>和多数据库实例没有关系，和数据库复制有关系。</p>
</blockquote>

</div><div id="wmd-preview-section-23" class="wmd-preview-section preview-content">

<h3 id="3-安装操作">3. 安装操作</h3>

</div><div id="wmd-preview-section-4614" class="wmd-preview-section preview-content">

<pre class="prettyprint hljs-dark"><code class="language-bash hljs"><span class="hljs-comment">#</span><br><span class="hljs-comment"># 多实例配置文件，可以mysqld_multi --example 查看例子</span><br><span class="hljs-comment">#</span><br>[root@MyServer /]&gt; cat /etc/my.cnf <br><span class="hljs-comment">#[client]           # 这个标签如果配置了用户和密码，</span><br>                    <span class="hljs-comment"># 并且[mysqld_multi]下没有配置用户名密码，</span><br>                    <span class="hljs-comment"># 则mysqld_multi stop时, 会使用这个密码</span><br>                    <span class="hljs-comment"># 如果没有精确的匹配，则匹配[client]标签</span><br><span class="hljs-comment">#user = root        </span><br><span class="hljs-comment">#password = 123</span><br><span class="hljs-comment">#-------------</span><br>[mysqld_multi]<br>mysqld = /usr/<span class="hljs-built_in">local</span>/mysql/bin/mysqld_safe<br>mysqladmin = /usr/<span class="hljs-built_in">local</span>/mysql/bin/mysqladmin<br>user = multi_admin<br>pass = <span class="hljs-number">123</span>  <span class="hljs-comment"># 官方文档中写的password，但是存在bug，需要改成pass(v5.7.9)</span><br>            <span class="hljs-comment"># 写成password，start时正常，stop时，报如下错误</span><br>            <span class="hljs-comment"># Access denied for user 'multi_admin'@'localhost' (using password: YES)</span><br><span class="hljs-built_in">log</span> = /var/<span class="hljs-built_in">log</span>/mysqld_multi.log<br><br><br>[mysqld1]  <span class="hljs-comment"># mysqld后面的数字为GNR, 是该实例的标识</span><br>           <span class="hljs-comment"># mysqld_multi  start 1,  mysqld_multi start 2-4</span><br>server-id = <span class="hljs-number">11</span><br>socket = /tmp/mysql.sock1<br>port = <span class="hljs-number">3306</span><br><span class="hljs-built_in">bind</span>_address = <span class="hljs-number">0.0</span>.<span class="hljs-number">0.0</span><br>datadir = /data1<br>user = mysql<br>performance_schema = off<br>innodb_buffer_pool_size = <span class="hljs-number">32</span>M<br>skip_name_resolve = <span class="hljs-number">1</span><br><span class="hljs-built_in">log</span>_error = error.log<br>pid-file = /data1/mysql.pid1<br><br><br>[mysqld2]<br>server-id = <span class="hljs-number">12</span><br>socket = /tmp/mysql.sock2<br>port = <span class="hljs-number">3307</span><br><span class="hljs-built_in">bind</span>_address = <span class="hljs-number">0.0</span>.<span class="hljs-number">0.0</span><br>datadir = /data2<br>user = mysql<br>performance_schema = off<br>innodb_buffer_pool_size = <span class="hljs-number">32</span>M<br>skip_name_resolve = <span class="hljs-number">1</span><br><span class="hljs-built_in">log</span>_error = error.log<br>pid-file = /data2/mysql.pid2<br><br><br>[mysqld3]<br>server-id = <span class="hljs-number">13</span><br>socket = /tmp/mysql.sock3<br>port = <span class="hljs-number">3308</span><br><span class="hljs-built_in">bind</span>_address = <span class="hljs-number">0.0</span>.<span class="hljs-number">0.0</span><br>datadir = /data3<br>user = mysql<br>performance_schema = off<br>innodb_buffer_pool_size = <span class="hljs-number">32</span>M<br>skip_name_resolve = <span class="hljs-number">1</span><br><span class="hljs-built_in">log</span>_error = error.log<br>pid-file = /data3/mysql.pid3<br><br><br>[mysqld4]<br>server-id = <span class="hljs-number">14</span><br>socket = /tmp/mysql.sock4<br>port = <span class="hljs-number">3309</span><br><span class="hljs-built_in">bind</span>_address = <span class="hljs-number">0.0</span>.<span class="hljs-number">0.0</span><br>datadir = /data4<br>user = mysql<br>performance_schema = off<br>innodb_buffer_pool_size = <span class="hljs-number">32</span>M<br>skip_name_resolve = <span class="hljs-number">1</span><br><span class="hljs-built_in">log</span>_error = error.log<br>pid-file = /data4/mysql.pid4<br></code></pre></div><div id="wmd-preview-section-9157" class="wmd-preview-section preview-content">

<pre class="prettyprint hljs-dark"><code class="language-bash hljs"><span class="hljs-comment">#</span><br><span class="hljs-comment"># 准备好数据目录，并初始化安装</span><br><span class="hljs-comment">#</span><br>[root@MyServer ~]&gt; mkdir /data1<br>[root@MyServer ~]&gt; mkdir /data2<br>[root@MyServer ~]&gt; mkdir /data3<br>[root@MyServer ~]&gt; mkdir /data4<br>[root@MyServer ~]&gt; chown mysql.mysql /data{<span class="hljs-number">1</span>..<span class="hljs-number">4</span>}<br>[root@MyServer ~]&gt; mysqld --initialize --user=mysql --datadir=/data1<br><span class="hljs-comment">#</span><br><span class="hljs-comment"># 一些日志输出，并提示临时密码，下同</span><br><span class="hljs-comment">#</span><br>[root@MyServer ~]&gt; mysqld --initialize --user=mysql --datadir=/data2<br>[root@MyServer ~]&gt; mysqld --initialize --user=mysql --datadir=/data3<br>[root@MyServer ~]&gt; mysqld --initialize --user=mysql --datadir=/data4<br><span class="hljs-comment"># 安装后，需要检查error.log 确保没有错误出现</span><br>[root@MyServer ~]&gt; cp /usr/<span class="hljs-built_in">local</span>/mysql/support-files/mysqld_multi.server  /etc/init.d/mysqld_multid <br><span class="hljs-comment"># 拷贝启动脚本，方便自启</span><br>[root@MyServer ~]&gt; chkconfig mysqld_multid on<br></code></pre></div><div id="wmd-preview-section-26" class="wmd-preview-section preview-content">

<pre class="prettyprint hljs-dark"><code class="language-bash hljs">[root@MyServer ~]&gt; mysqld_multi  start<br>[root@MyServer ~]&gt; mysqld_multi  report<br>Reporting MySQL servers<br>MySQL server from group: mysqld1 is running<br>MySQL server from group: mysqld2 is running<br>MySQL server from group: mysqld3 is running<br>MySQL server from group: mysqld4 is running<br>[root@MyServer ~]&gt; netstat -tunlp | grep mysql<br>[root@MyServer ~]&gt; netstat -tunlp | grep mysql<br>tcp        <span class="hljs-number">0</span>      <span class="hljs-number">0</span> :::<span class="hljs-number">3307</span>                     :::*                        LISTEN      <span class="hljs-number">6221</span>/mysqld         <br>tcp        <span class="hljs-number">0</span>      <span class="hljs-number">0</span> :::<span class="hljs-number">3308</span>                     :::*                        LISTEN      <span class="hljs-number">6232</span>/mysqld         <br>tcp        <span class="hljs-number">0</span>      <span class="hljs-number">0</span> :::<span class="hljs-number">3309</span>                     :::*                        LISTEN      <span class="hljs-number">6238</span>/mysqld         <br>tcp        <span class="hljs-number">0</span>      <span class="hljs-number">0</span> :::<span class="hljs-number">3306</span>                     :::*                        LISTEN      <span class="hljs-number">6201</span>/mysqld         <br><br>[root@MyServer ~]&gt; mysql -u root -S /tmp/mysql.sock1 -p -P3306<br><span class="hljs-comment">#</span><br><span class="hljs-comment"># 使用-S /tmp/mysql.sock1 进行登录，并输入临时密码后，修改密码，下同</span><br><span class="hljs-comment">#</span><br>[root@MyServer ~]&gt; mysql -u root -S /tmp/mysql.sock2 -p -P3307<br>[root@MyServer ~]&gt; mysql -u root -S /tmp/mysql.sock3 -p -P3308<br>[root@MyServer ~]&gt; mysql -u root -S /tmp/mysql.sock4 -p -P3309<br></code></pre>

</div><div id="wmd-preview-section-4797" class="wmd-preview-section preview-content">

<pre class="prettyprint hljs-dark"><code class="language-sql hljs"><span class="hljs-comment">--</span><br><span class="hljs-comment">-- mysql1</span><br><span class="hljs-comment">--</span><br>mysql&gt; <span class="hljs-operator"><span class="hljs-keyword">show</span> <span class="hljs-keyword">variables</span> <span class="hljs-keyword">like</span> <span class="hljs-string">"port"</span>;</span> <br>+<span class="hljs-comment">---------------+-------+</span><br>| Variable_name | Value |<br>+<span class="hljs-comment">---------------+-------+</span><br>| port          | 3306  |<br>+<span class="hljs-comment">---------------+-------+</span><br>1 row in <span class="hljs-operator"><span class="hljs-keyword">set</span> (<span class="hljs-number">0.00</span> sec)<br><br>mysql&gt; <span class="hljs-keyword">show</span> <span class="hljs-keyword">variables</span> <span class="hljs-keyword">like</span> <span class="hljs-string">"socket"</span>;</span><br>+<span class="hljs-comment">---------------+------------------+</span><br>| Variable_name | Value            |<br>+<span class="hljs-comment">---------------+------------------+</span><br>| socket        | /tmp/mysql.sock1 |<br>+<span class="hljs-comment">---------------+------------------+</span><br>1 row in <span class="hljs-operator"><span class="hljs-keyword">set</span> (<span class="hljs-number">0.01</span> sec)<br><br>mysql&gt; <span class="hljs-keyword">show</span> <span class="hljs-keyword">variables</span> <span class="hljs-keyword">like</span> <span class="hljs-string">"datadir"</span>;</span><br>+<span class="hljs-comment">---------------+---------+</span><br>| Variable_name | Value   |<br>+<span class="hljs-comment">---------------+---------+</span><br>| datadir       | /data1/ |<br>+<span class="hljs-comment">---------------+---------+</span><br>1 row in <span class="hljs-operator"><span class="hljs-keyword">set</span> (<span class="hljs-number">0.00</span> sec)<br><br><span class="hljs-comment">--</span><br><span class="hljs-comment">-- 这样才能进行关闭数据库的操作</span><br><span class="hljs-comment">-- 和[mysqld_multi]中的user，pass(注意在5.7.9中不是password)对应起来 （类比[client]标签）</span><br><span class="hljs-comment">-- 一会测试federated链接，需要增加federated参数，并重启mysql2</span><br><span class="hljs-comment">--</span><br>mysql&gt; <span class="hljs-keyword">create</span> <span class="hljs-keyword">user</span> <span class="hljs-string">'multi_admin'</span>@<span class="hljs-string">'localhost'</span> <span class="hljs-keyword">identified</span> <span class="hljs-keyword">by</span> <span class="hljs-string">'123'</span>;</span><br>Query OK, 0 rows affected (0.00 sec)<br>mysql&gt; <span class="hljs-operator"><span class="hljs-keyword">grant</span> shutdown <span class="hljs-keyword">on</span> *.* <span class="hljs-keyword">to</span> <span class="hljs-string">'multi_admin'</span>@<span class="hljs-string">'localhost'</span>;</span><br><br><span class="hljs-comment">--</span><br><span class="hljs-comment">-- mysql2, mysql3, mysql4 类似。可以看到与my.cnf中对应的port和socket</span><br><span class="hljs-comment">--</span><br></code></pre></div><div id="wmd-preview-section-28" class="wmd-preview-section preview-content">

<h2 id="六-federated测试">六. Federated测试</h2>

</div><div id="wmd-preview-section-29" class="wmd-preview-section preview-content">

<pre class="prettyprint hljs-dark"><code class="language-sql hljs"><span class="hljs-comment">--</span><br><span class="hljs-comment">-- mysql1 准备数据</span><br><span class="hljs-comment">--</span><br>mysql&gt; <span class="hljs-operator"><span class="hljs-keyword">show</span> <span class="hljs-keyword">variables</span> <span class="hljs-keyword">like</span> <span class="hljs-string">"port"</span>;</span> <br>+<span class="hljs-comment">---------------+-------+</span><br>| Variable_name | Value |<br>+<span class="hljs-comment">---------------+-------+</span><br>| port          | 3306  |   <span class="hljs-comment">-- mysql1 实例端口</span><br>+<span class="hljs-comment">---------------+-------+</span><br>1 row in <span class="hljs-operator"><span class="hljs-keyword">set</span> (<span class="hljs-number">0.00</span> sec)<br><br>mysql&gt; <span class="hljs-keyword">create</span> <span class="hljs-keyword">database</span> burn;</span><br>Query OK, 1 row affected (0.00 sec)<br><br>mysql&gt; <span class="hljs-operator"><span class="hljs-keyword">use</span> burn;</span><br>Database changed<br><br>mysql&gt; <span class="hljs-operator"><span class="hljs-keyword">create</span> <span class="hljs-keyword">table</span> book (<br>    -&gt; id <span class="hljs-built_in">int</span> <span class="hljs-keyword">not</span> <span class="hljs-literal">null</span> auto_increment,<br>    -&gt; name <span class="hljs-built_in">varchar</span>(<span class="hljs-number">128</span>) <span class="hljs-keyword">not</span> <span class="hljs-literal">null</span>,<br>    -&gt; <span class="hljs-keyword">primary</span> <span class="hljs-keyword">key</span>(id)<br>    -&gt; );</span><br>Query OK, 0 rows affected (0.20 sec)<br><br>mysql&gt; <span class="hljs-operator"><span class="hljs-keyword">insert</span> <span class="hljs-keyword">into</span> book <span class="hljs-keyword">values</span>(<span class="hljs-number">1</span>, <span class="hljs-string">"book1"</span>);</span><br>Query OK, 1 row affected (0.02 sec)<br><br>mysql&gt; <span class="hljs-operator"><span class="hljs-keyword">select</span> * <span class="hljs-keyword">from</span> book;</span><br>+<span class="hljs-comment">----+-------+</span><br>| id | name  |<br>+<span class="hljs-comment">----+-------+</span><br>|  1 | book1 |<br>+<span class="hljs-comment">----+-------+</span><br>1 row in <span class="hljs-operator"><span class="hljs-keyword">set</span> (<span class="hljs-number">0.00</span> sec)<br><br>mysql&gt; <span class="hljs-keyword">create</span> <span class="hljs-keyword">user</span> <span class="hljs-string">'burn'</span>@<span class="hljs-string">'127.0.0.1'</span> <span class="hljs-keyword">identified</span> <span class="hljs-keyword">by</span> <span class="hljs-string">'123'</span>;</span><br>Query OK, 0 rows affected (0.00 sec)<br><br>mysql&gt; <span class="hljs-operator"><span class="hljs-keyword">grant</span> <span class="hljs-keyword">select</span> <span class="hljs-keyword">on</span> burn.* <span class="hljs-keyword">to</span> <span class="hljs-string">'burn'</span>@<span class="hljs-string">'127.0.0.1'</span>;</span><br>Query OK, 0 rows affected (0.00 sec)<br><br>mysql&gt; <span class="hljs-operator"><span class="hljs-keyword">show</span> <span class="hljs-keyword">grants</span> <span class="hljs-keyword">for</span> <span class="hljs-string">'burn'</span>@<span class="hljs-string">'127.0.0.1'</span>;</span><br>+<span class="hljs-comment">------------------------------------------------+</span><br>| Grants for burn@127.0.0.1                      |<br>+<span class="hljs-comment">------------------------------------------------+</span><br>| <span class="hljs-operator"><span class="hljs-keyword">GRANT</span> <span class="hljs-keyword">USAGE</span> <span class="hljs-keyword">ON</span> *.* <span class="hljs-keyword">TO</span> <span class="hljs-string">'burn'</span>@<span class="hljs-string">'127.0.0.1'</span>       |<br>| <span class="hljs-keyword">GRANT</span> <span class="hljs-keyword">SELECT</span> <span class="hljs-keyword">ON</span> <span class="hljs-string">`burn`</span>.* <span class="hljs-keyword">TO</span> <span class="hljs-string">'burn'</span>@<span class="hljs-string">'127.0.0.1'</span> |<br>+<span class="hljs-comment">------------------------------------------------+</span><br><span class="hljs-number">2</span> <span class="hljs-keyword">rows</span> <span class="hljs-keyword">in</span> <span class="hljs-keyword">set</span> (<span class="hljs-number">0.00</span> sec)</span><br></code></pre>

</div><div id="wmd-preview-section-7498" class="wmd-preview-section preview-content">

<pre class="prettyprint hljs-dark"><code class="language-sql hljs"><span class="hljs-comment">--</span><br><span class="hljs-comment">-- mysql2 测试Federated</span><br><span class="hljs-comment">--</span><br>mysql&gt; <span class="hljs-operator"><span class="hljs-keyword">show</span> <span class="hljs-keyword">variables</span> <span class="hljs-keyword">like</span> <span class="hljs-string">"port"</span>;</span><br>+<span class="hljs-comment">---------------+-------+</span><br>| Variable_name | Value |<br>+<span class="hljs-comment">---------------+-------+</span><br>| port          | 3307  |  <span class="hljs-comment">-- msyql2 实例端口</span><br>+<span class="hljs-comment">---------------+-------+</span><br>1 row in <span class="hljs-operator"><span class="hljs-keyword">set</span> (<span class="hljs-number">0.01</span> sec)<br><br>mysql&gt; <span class="hljs-keyword">show</span> <span class="hljs-keyword">engines</span>;</span><br>+<span class="hljs-comment">--------------------+---------+----------------------------------------------------------------+--------------+------+------------+</span><br>| Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |<br>+<span class="hljs-comment">--------------------+---------+----------------------------------------------------------------+--------------+------+------------+</span><br>| MyISAM             | YES     | MyISAM storage engine                                          | NO           | NO   | NO         |<br>| CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         |<br>| PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         |<br>| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |<br>| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |<br>| InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |<br>| ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         |<br>| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |<br>| FEDERATED          | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |<br>+<span class="hljs-comment">--------------------+---------+----------------------------------------------------------------+--------------+------+------------+</span><br><span class="hljs-comment">--</span><br><span class="hljs-comment">-- federated 引擎没有打开</span><br><span class="hljs-comment">--</span><br>9 rows in <span class="hljs-operator"><span class="hljs-keyword">set</span> (<span class="hljs-number">0.00</span> sec)<br><br></span><br></code></pre></div><div id="wmd-preview-section-7947" class="wmd-preview-section preview-content">

<pre class="prettyprint hljs-dark"><code class="language-bash hljs"><span class="hljs-comment">#</span><br><span class="hljs-comment"># 在[mysqld2]标签下面增加federated</span><br><span class="hljs-comment">#</span><br><br>[root@MyServer ~]&gt; cat /etc/my.cnf<br><span class="hljs-comment"># ... 省略 ...</span><br>[mysqld2]<br>federated <span class="hljs-comment"># 新增的配置项，表示打开federated引擎</span><br><span class="hljs-comment"># ... 省略 ...</span><br>[root@MyServer ~]&gt; mysqld_multi stop <span class="hljs-number">2</span><br>[root@MyServer ~]&gt; mysqld_multi start <span class="hljs-number">2</span>   <span class="hljs-comment"># 重启配置</span><br></code></pre>

</div><div id="wmd-preview-section-10956" class="wmd-preview-section preview-content">

<pre class="prettyprint hljs-dark"><code class="language-sql hljs">mysql&gt; <span class="hljs-operator"><span class="hljs-keyword">show</span> <span class="hljs-keyword">variables</span> <span class="hljs-keyword">like</span> <span class="hljs-string">"port"</span>;</span><br>+<span class="hljs-comment">---------------+-------+</span><br>| Variable_name | Value |<br>+<span class="hljs-comment">---------------+-------+</span><br>| port          | 3307  |  <span class="hljs-comment">-- msyql2 实例端口</span><br>+<span class="hljs-comment">---------------+-------+</span><br>1 row in <span class="hljs-operator"><span class="hljs-keyword">set</span> (<span class="hljs-number">0.01</span> sec)<br><br>mysql&gt; <span class="hljs-keyword">show</span> <span class="hljs-keyword">engines</span>;</span><br>+<span class="hljs-comment">--------------------+---------+----------------------------------------------------------------+--------------+------+------------+</span><br>| Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |<br>+<span class="hljs-comment">--------------------+---------+----------------------------------------------------------------+--------------+------+------------+</span><br>| MyISAM             | YES     | MyISAM storage engine                                          | NO           | NO   | NO         |<br>| CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         |<br>| PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         |<br>| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |<br>| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |<br>| InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |<br>| ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         |<br>| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |<br>| FEDERATED          | YES     | Federated MySQL storage engine                                 | NO           | NO   | NO         |<br>+<span class="hljs-comment">--------------------+---------+----------------------------------------------------------------+--------------+------+------------+</span><br>9 rows in <span class="hljs-operator"><span class="hljs-keyword">set</span> (<span class="hljs-number">0.00</span> sec)<br><span class="hljs-comment">--</span><br><span class="hljs-comment">-- 显示 federated 已经启用</span><br><span class="hljs-comment">--</span><br>mysql&gt; <span class="hljs-keyword">create</span> <span class="hljs-keyword">database</span> federated_test;</span><br>Query OK, 1 row affected (0.00 sec)<br><br>mysql&gt; <span class="hljs-operator"><span class="hljs-keyword">use</span> federated_test;</span><br>Database changed<br><br>mysql&gt; <span class="hljs-operator"><span class="hljs-keyword">create</span> <span class="hljs-keyword">table</span> federated_table_1 (<br>    -&gt; id <span class="hljs-built_in">int</span> <span class="hljs-keyword">not</span> <span class="hljs-literal">null</span> auto_increment,<br>    -&gt; name <span class="hljs-built_in">varchar</span>(<span class="hljs-number">128</span>) <span class="hljs-keyword">not</span> <span class="hljs-literal">null</span>,<br>    -&gt; <span class="hljs-keyword">primary</span> <span class="hljs-keyword">key</span>(id)<br>    -&gt; ) <span class="hljs-keyword">engine</span>=federated<br>    -&gt; <span class="hljs-keyword">connection</span>=<span class="hljs-string">'mysql://burn:123@127.0.0.1:3306/burn/book'</span>;</span><br>Query OK, 0 rows affected (0.04 sec)<br><br>mysql&gt; <span class="hljs-operator"><span class="hljs-keyword">select</span> * <span class="hljs-keyword">from</span> federated_table_1;</span><br>+<span class="hljs-comment">----+-------+</span><br>| id | name  |<br>+<span class="hljs-comment">----+-------+</span><br>|  1 | book1 |  <span class="hljs-comment">-- 和 mysqld1 上的内容一致。</span><br>+<span class="hljs-comment">----+-------+</span><br>1 row in <span class="hljs-operator"><span class="hljs-keyword">set</span> (<span class="hljs-number">0.00</span> sec)<br><span class="hljs-comment">--</span><br><span class="hljs-comment">-- 由于只有select权限，无法对该表进行insert操作</span><br><span class="hljs-comment">--</span><br>mysql&gt; <span class="hljs-keyword">insert</span> <span class="hljs-keyword">into</span>  federated_table_1 <span class="hljs-keyword">values</span>(<span class="hljs-number">2</span>, <span class="hljs-string">"book2"</span>);</span><br>ERROR 1296 (HY000): Got error 10000 'Error on remote system: 1142: <span class="hljs-operator"><span class="hljs-keyword">INSERT</span> command denied <span class="hljs-keyword">to</span> <span class="hljs-keyword">user</span> <span class="hljs-string">'burn'</span>@<span class="hljs-string">'127.0.0.1'</span> <span class="hljs-keyword">for</span> <span class="hljs-keyword">table</span> <span class="hljs-string">'book'' from FEDERATED</span></span><br></code></pre></div><div id="wmd-preview-section-footnotes" class="preview-content"></div></div></body></html>